package studio7i.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import studio7i.excepcion.DAOExcepcion;
import studio7i.modelo.Instrumento;
import studio7i.modelo.Sala;
import studio7i.modelo.Local;
import studio7i.util.ConexionBD;

public class SalaDAO extends BaseDAO {
	
	
	public void insertar(Sala oSal) throws DAOExcepcion {
		System.out.println("SalaDAO: insertar(Sala oSal)");
		String query = "";
		
		query +="INSERT INTO SALA(IDLOCAL,CAPACIDADINSTRUMENTOS,";
		query +="CAPACIDADPERSONAS,CARACTERISTICAS,COSTOXHORA,NOMSALA)";
		query +="VALUES(?,?,?,?,?,?)";
		
		
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			
			stmt.setInt(1,oSal.getLocal().getIdLocal());
			stmt.setInt(2, oSal.getCapacidadInstrumentos());
			stmt.setInt(3, oSal.getCapacidadPersonas());
			stmt.setString(4,oSal.getCaracteristicas());
			stmt.setDouble(5,oSal.getCostoxhora());	
			stmt.setString(6, oSal.getNombre());
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
	
	public int validar_nombre_sala(Sala oSala)throws DAOExcepcion  {
		int intReturn=0;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		String query = "";
		query +="SELECT COUNT(*) AS CANTIDAD FROM SALA WHERE ";
		query +="IDLOCAL=? AND NOMSALA=?";
		
		try{
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			
			stmt.setInt(1,oSala.getLocal().getIdLocal());
			stmt.setString(2, oSala.getNombre());
			
			rs = stmt.executeQuery();
			while (rs.next()) {
				intReturn=rs.getInt("CANTIDAD");				
			}			
		}
		catch (SQLException e)
		{
			throw new DAOExcepcion(e.getMessage());
		}
		finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}		
		return intReturn;		
	}
	
	public void armar(Sala sala) throws DAOExcepcion {
		
		Collection<Instrumento> ins=sala.getInstrumentos();
		Iterator iterador = ins.iterator();  
		     
		while (iterador.hasNext()){
				
		String query = "";
		query +="INSERT INTO INSTRUMENTO_SALA (IDINSTRUMENTO, IDSALA)";
		query +=" VALUES(?,?)";		
		
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			
		    Instrumento in=new Instrumento();
		    in=(Instrumento) iterador.next();
			stmt.setInt(1, in.getIdInstrumento());

			stmt.setInt(2,sala.getIdSala());			
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		
		}
	}

	public Collection<Sala> buscarSalas(Sala sala) throws DAOExcepcion {
		String query;
		
		Collection<Sala> s = new ArrayList<Sala>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			con = ConexionBD.obtenerConexion();
					
			query = "select l.idlocal, l.descripcion,l.ubicacion, s.idsala, s.caracteristicas, s.costoxhora , s.capacidadinstrumentos, s.capacidadpersonas from sala s,local l where s.idlocal = l.idlocal ";
			query += "and capacidadpersonas >= ? and  capacidadinstrumentos >= ? and costoxhora <= ? ";
			
			if(sala.getLocal().getIdLocal()==0){
			}else{
				query += " and s.idlocal = ? ";
			}
			

			stmt = con.prepareStatement(query);
			stmt.setInt(1,sala.getCapacidadPersonas());
			stmt.setInt(2,sala.getCapacidadInstrumentos());
			stmt.setDouble(3,sala.getCostoxhora());
			if(sala.getLocal().getIdLocal()==0){
			}else{
				stmt.setInt(4,sala.getLocal().getIdLocal());
			}

			rs = stmt.executeQuery();
			
			while (rs.next()) {
					Local lo = new Local();
					Sala sa = new Sala();
					lo.setIdLocal(rs.getInt("IDLOCAL"));
					lo.setDescripcion(rs.getString("DESCRIPCION"));
					lo.setUbicacion(rs.getString("UBICACION"));
					sa.setIdSala(rs.getInt("IDSALA"));
					sa.setNombre(rs.getString("CARACTERISTICAS"));
					sa.setCapacidadInstrumentos(rs.getInt("CAPACIDADINSTRUMENTOS"));
					sa.setCapacidadPersonas(rs.getInt("capacidadPersonas"));
					sa.setCostoxhora(rs.getDouble("COSTOXHORA"));
					sa.setLocal(lo);

					s.add(sa);
				} 
			}catch (SQLException e) {
				System.err.println(e.getMessage());
				throw new DAOExcepcion(e.getMessage());
			} finally {
				this.cerrarStatement(stmt);
				this.cerrarConexion(con);
			}
			return s;	
	}
	
	public Collection<Sala> listarSalas() throws DAOExcepcion {
		String query;
		
		Collection<Sala> s = new ArrayList<Sala>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			con = ConexionBD.obtenerConexion();
			query = "select * from sala";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();

			while (rs.next()) {
					Sala sa = new Sala();
					Local lo= new Local();
					lo.setIdLocal(rs.getInt("idLocal"));
					sa.setIdSala(rs.getInt("idSala"));
					sa.setCapacidadInstrumentos(rs.getInt("capacidadInstrumentos"));
					sa.setCapacidadPersonas(rs.getInt("capacidadPersonas"));
					sa.setCaracteristicas(rs.getString("caracteristicas"));
					sa.setCostoxhora(rs.getDouble("COSTOXHORA"));
					s.add(sa);
				} 
			}catch (SQLException e) {
				System.err.println(e.getMessage());
				throw new DAOExcepcion(e.getMessage());
			} finally {
				this.cerrarStatement(stmt);
				this.cerrarConexion(con);
			}
			return s;	
	}
	
	public Sala getSala(int idSala) throws DAOExcepcion {
		String query;		
		//Collection<Sala> s = new ArrayList<Sala>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Sala sa = new Sala();
		
		try {
			con = ConexionBD.obtenerConexion();
			query = "select * from sala where idsala=?";
			stmt = con.prepareStatement(query);
			stmt.setInt(1,idSala);
			rs = stmt.executeQuery();
			
			while (rs.next()) {					
					Local lo= new Local();
					lo.setIdLocal(rs.getInt("idLocal"));
					sa.setIdSala(rs.getInt("idSala"));
					sa.setCapacidadInstrumentos(rs.getInt("capacidadInstrumentos"));
					sa.setCapacidadPersonas(rs.getInt("capacidadPersonas"));
					sa.setCaracteristicas(rs.getString("caracteristicas"));
					sa.setCostoxhora(rs.getDouble("COSTOXHORA"));
					//s.add(sa);
				} 
			}catch (SQLException e) {
				System.err.println(e.getMessage());
				throw new DAOExcepcion(e.getMessage());
			} finally {
				this.cerrarStatement(stmt);
				this.cerrarConexion(con);
			}
			return sa;	
	}

}
